create external table jms_dm.dm_all_transfer_count_site_dt(
date_time date COMMENT '日期' ,
taking_code      string COMMENT '揽收/始发网点code' ,
taking_name string COMMENT '揽收/始发网点name' ,
taking_network_type  string COMMENT '揽收/始发网点类型，网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点' ,
taking_fran_code   string COMMENT '揽收/始发网点加盟商' ,
taking_fran_name    string COMMENT '揽收/始发网点加盟商code' ,
taking_agent_code   string COMMENT '代理区编码' ,
taking_agent_name    string COMMENT '代理区名称',
taking_manage_code   string COMMENT '管理大区名称',
taking_manage_name    string COMMENT '管理大区名称',
taking_provider_id     string COMMENT '省份ID',
taking_provider_desc  string COMMENT '省份',
taking_city_id         string COMMENT '城市ID',
taking_city_desc    string COMMENT '城市',
taking_area_id     string COMMENT '区/县ID',
taking_area_desc    string COMMENT '区/县',
sign_network_code  string COMMENT '签收/目的网点' ,
sign_network_name   string COMMENT '签收/目的网点name' ,
sign_network_type   string COMMENT '签收/目的网点类型' ,
sign_fran_code   string COMMENT '签收/目的网点加盟商' ,
sign_fran_name    string COMMENT '签收/目的网点加盟商code' ,
sign_agent_code   string COMMENT '代理区编码' ,
sign_agent_name    string COMMENT '代理区名称',
sign_manage_code   string COMMENT '管理大区名称',
sign_manage_name    string COMMENT '管理大区名称',
sign_provider_id     string COMMENT '省份ID',
sign_provider_desc  string COMMENT '省份',
sign_city_id         string COMMENT '城市ID',
sign_city_desc    string COMMENT '城市',
sign_area_id     string COMMENT '区/县ID',
sign_area_desc    string COMMENT '区/县',
first_center_network_code  string COMMENT '始发中心' ,
first_center_network_name string COMMENT '始发中心name' ,
end_center_network_code  string COMMENT '目的中心' ,
end_center_network_name string COMMENT '目的中心name' ,
front_number          bigint COMMENT '正向-件量',
front_plan_zz         decimal(16,4) COMMENT '正向-规划中转次数',
front_actual_zz       decimal(16,4) COMMENT '正向-实际中转次数',
front_agent_in      bigint COMMENT '正向-全部流向-代理区内中转次数' ,
front_agent_out bigint COMMENT '正向-全部流向-代理区外中转次数' ,
front_reback  bigint COMMENT '正向-异常件-退件件量' ,
front_reback_zz  decimal(16,4) COMMENT '正向-异常件-退件中转次数' ,
front_rational  bigint COMMENT '正向-异常件-不合理中转件量' ,
front_rational_zz  decimal(16,4) COMMENT '正向-异常件-不合理中转次数' ,
front_proble  bigint COMMENT '正向-异常件-问题件件量' ,
front_proble_zz  decimal(16,4) COMMENT '正向-异常件-问题件中转次数' ,
back_number          bigint COMMENT '反向-件量',
back_plan_zz         decimal(16,4) COMMENT '反向-规划中转次数',
back_actual_zz       decimal(16,4) COMMENT '反向-实际中转次数',
back_agent_in  bigint COMMENT '反向-全部流向-代理区内中转次数' ,
back_agent_out bigint COMMENT '反向-全部流向-代理区外中转次数' ,
back_reback  bigint COMMENT '反向-异常件-退件件量' ,
back_reback_zz  decimal(16,4) COMMENT '反向-异常件-退件中转次数' ,
back_rational  bigint COMMENT '反向-异常件-不合理中转件量' ,
back_rational_zz  decimal(16,4) COMMENT '反向-异常件-不合理中转次数' ,
back_proble  bigint COMMENT '反向-异常件-问题件件量' ,
back_proble_zz  decimal(16,4) COMMENT '反向-异常件-问题件中转次数'
)
comment '全程中转次数报表-网点层级'
PARTITIONED BY ( dt string COMMENT '时间分区')
stored as parquet
LOCATION '/dw/hive/jms_dm.db/external/dm_all_transfer_count_site_dt'
TBLPROPERTIES (     'discover.partitions' = 'false',  'parquet.column.index.access' = 'true');




create  table jms_dm.dm_all_transfer_count_site_dt(
date_time date COMMENT '日期' ,
taking_code      varchar(100) COMMENT '揽收/始发网点code' ,
taking_name varchar(100) COMMENT '揽收/始发网点name' ,
taking_network_type  varchar(100) COMMENT '揽收/始发网点类型，网点类型:1总部,2代理区,3加盟商,4中心,5集散点,6网点' ,
taking_fran_code   varchar(100) COMMENT '揽收/始发网点加盟商' ,
taking_fran_name    varchar(100) COMMENT '揽收/始发网点加盟商code' ,
taking_agent_code   varchar(100) COMMENT '代理区编码' ,
taking_agent_name    varchar(100) COMMENT '代理区名称',
taking_manage_code   varchar(100) COMMENT '管理大区名称',
taking_manage_name    varchar(100) COMMENT '管理大区名称',
taking_provider_id     varchar(100) COMMENT '省份ID',
taking_provider_desc  varchar(100) COMMENT '省份',
taking_city_id         varchar(100) COMMENT '城市ID',
taking_city_desc    varchar(100) COMMENT '城市',
taking_area_id     varchar(100) COMMENT '区/县ID',
taking_area_desc    varchar(100) COMMENT '区/县',
sign_network_code  varchar(100) COMMENT '签收/目的网点' ,
sign_network_name   varchar(100) COMMENT '签收/目的网点name' ,
sign_network_type  varchar(100) COMMENT '签收/目的网点类型' ,
sign_fran_code   varchar(100) COMMENT '签收/目的网点加盟商' ,
sign_fran_name    varchar(100) COMMENT '签收/目的网点加盟商code' ,
sign_agent_code   varchar(100) COMMENT '代理区编码' ,
sign_agent_name    varchar(100) COMMENT '代理区名称',
sign_manage_code   varchar(100) COMMENT '管理大区名称',
sign_manage_name    varchar(100) COMMENT '管理大区名称',
sign_provider_id     varchar(100) COMMENT '省份ID',
sign_provider_desc  varchar(100) COMMENT '省份',
sign_city_id         varchar(100) COMMENT '城市ID',
sign_city_desc    varchar(100) COMMENT '城市',
sign_area_id     varchar(100) COMMENT '区/县ID',
sign_area_desc    varchar(100) COMMENT '区/县',
first_center_network_code  varchar(100) COMMENT '始发中心' ,
first_center_network_name varchar(100) COMMENT '始发中心name' ,
end_center_network_code  varchar(100) COMMENT '目的中心' ,
end_center_network_name varchar(100) COMMENT '目的中心name' ,
front_number          bigint COMMENT '正向-件量',
front_plan_zz         decimal(16,4) COMMENT '正向-规划中转次数',
front_actual_zz       decimal(16,4) COMMENT '正向-实际中转次数',
front_agent_in      bigint COMMENT '正向-全部流向-代理区内中转次数' ,
front_agent_out bigint COMMENT '正向-全部流向-代理区外中转次数' ,
front_reback  bigint COMMENT '正向-异常件-退件件量' ,
front_reback_zz  decimal(16,4) COMMENT '正向-异常件-退件中转次数' ,
front_rational  bigint COMMENT '正向-异常件-不合理中转件量' ,
front_rational_zz  decimal(16,4) COMMENT '正向-异常件-不合理中转次数' ,
front_proble  bigint COMMENT '正向-异常件-问题件件量' ,
front_proble_zz  decimal(16,4) COMMENT '正向-异常件-问题件中转次数' ,
back_number          bigint COMMENT '反向-件量',
back_plan_zz         decimal(16,4) COMMENT '反向-规划中转次数',
back_actual_zz       decimal(16,4) COMMENT '反向-实际中转次数',
back_agent_in  bigint COMMENT '反向-全部流向-代理区内中转次数' ,
back_agent_out bigint COMMENT '反向-全部流向-代理区外中转次数' ,
back_reback  bigint COMMENT '反向-异常件-退件件量' ,
back_reback_zz  decimal(16,4) COMMENT '反向-异常件-退件中转次数' ,
back_rational  bigint COMMENT '反向-异常件-不合理中转件量' ,
back_rational_zz  decimal(16,4) COMMENT '反向-异常件-不合理中转次数' ,
back_proble  bigint COMMENT '反向-异常件-问题件件量' ,
back_proble_zz  decimal(16,4) COMMENT '反向-异常件-问题件中转次数'
)
ENGINE = OLAP
DUPLICATE KEY( date_time,taking_code)
COMMENT "全程中转次数报表-网点层级"
PARTITION BY RANGE(date_time)
(START ("2022-07-31") END ("2022-08-25") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`taking_code`,sign_network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);


alter table bidefault.dm_all_transfer_count_site_dt add columns(front_is_wrong bigint comment '正向-错发-件量') cascade
alter table bidefault.dm_all_transfer_count_site_dt add columns(front_is_wrong_zz decimal(16,4) comment '正向-错发-中转次数') cascade

alter table bidefault.dm_all_transfer_count_site_dt add columns(back_is_wrong bigint comment '反向-错发-件量') cascade
alter table bidefault.dm_all_transfer_count_site_dt add columns(back_is_wrong_zz decimal(16,4) comment '反向-错发-中转次数') cascade




alter table bidefault.dm_all_transfer_count_site_dt add columns(front_bak_route bigint comment '正向-备用路由-件量') cascade
alter table bidefault.dm_all_transfer_count_site_dt add columns(front_bak_route_zz decimal(16,4) comment '正向-备用路由-中转次数') cascade
alter table bidefault.dm_all_transfer_count_site_dt add columns(back_bak_route bigint comment '反向-备用路由-件量') cascade
alter table bidefault.dm_all_transfer_count_site_dt add columns(back_bak_route_zz decimal(16,4) comment '反向-备用路由-中转次数') cascade



alter table jms_dm.dm_all_transfer_count_site_dt add columns(
 end_nodal_network_code string comment '目的集散code'
,end_nodal_network_name string comment '目的集散name'
) cascade
